﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using System.Linq;
using System.Text;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using StoreTransport.Data;
using StoreTransport.DAO;
using StoreTransport.Business.Constants;

namespace StoreTransport.Business.Helper {
    public class ExcelHelper {
        private const string ExportTimeFormat = "dd-MM-yyyy";
        private const string ProductRecievePrefix = "HV-";

        public static bool ReadInputExcelFile(string filePath, ProductLine pl) {
            int initRow = 3;
            int initColumn = 6;
            Application app = null;
            Workbooks wbks = null;
            _Workbook _wbk = null;
            Sheets shs = null;
            _Worksheet _wsh = null;
            Dictionary<int, Market> missMarket = new Dictionary<int, Market>();
            Dictionary<int, Product> missProducts = new Dictionary<int, Product>();
            Dictionary<int, Market> existedMarkets = new Dictionary<int, Market>();
            Dictionary<int, string> existedMarketIdName = new Dictionary<int, string>();
            Dictionary<int, string> DicProductCode = new Dictionary<int, string>();
            Dictionary<int, Product> DicProduct = new Dictionary<int, Product>();
            Dictionary<string, MarketProductImport> DicData = new Dictionary<string, MarketProductImport>();
            Dictionary<int, ProductOrder> DicStoreProduct = new Dictionary<int, ProductOrder>();
            Dictionary<int, int> DicStoreInventory = new Dictionary<int, int>();
            Dictionary<int, double> sumSaleRate = new Dictionary<int, double>();
            Dictionary<int, int> productRecieveDic = new Dictionary<int, int>();
            DateTime lastUpdate = DateTime.Now;
            try {
                StoreTransportDataContext context = CommonHelper.GetContext();
                app = new Application();
                wbks = app.Workbooks;
                _wbk = wbks.Add(filePath);
                shs = _wbk.Sheets;
                _wsh = (_Worksheet)shs.get_Item(1);
                Range range = _wsh.UsedRange;

                // get all product code and row index in excel file.
                for (int i = initRow; i < range.Rows.Count; i++) {
                    string code = (string)(range.Cells[i, 1] as Range).Value2;
                    if (string.IsNullOrEmpty(code)) {
                        break;
                    }
                    DicProductCode.Add(i, code);
                }

                DicProduct = UpdateProducts(pl, DicProductCode, DicProduct, context, range);

                // get Market to check with data in excel file
                var mQuery = context.Markets;
                existedMarkets = mQuery.ToDictionary(c => c.ID);
                existedMarketIdName = mQuery.ToDictionary(m => m.ID, m => m.MarketName);
                //set empty for unimport product

                var allProducts = context.MarketProductImports.Where(rr => pl.ID == rr.ProductLineId);

                foreach (var p in allProducts) {
                    p.StoreAmount = 0;
                    p.SaleRate = 0;
                }
                context.SubmitChanges();
                // get Link between Product and market,using for allocation and transport
                DicData = context.MarketProductImports.Where(r => r.ProductLineId == pl.ID
                                                && existedMarkets.Keys.Contains(r.MarketId)
                                                && DicProduct.Keys.Contains(r.ProductId)).
                                                ToDictionary(r => r.MarketId + "-" + r.ProductId);
                // get All products in store center
                DicStoreProduct = context.ProductOrders.Where(mp => pl.ID == mp.ProductLineId)
                                                      .ToDictionary(ro => ro.ProductId);
                foreach (ProductOrder order in DicStoreProduct.Values) {
                    order.ComputedInventory = 0;
                    order.ImportInventory = 0;
                    order.SumSaleRate = 0;
                }
                context.SubmitChanges();

                Dictionary<string, int> marketNameIDs = new Dictionary<string, int>();
                foreach (KeyValuePair<int, string> pair in existedMarketIdName) {
                    if (!marketNameIDs.ContainsKey(pair.Value)) {
                        marketNameIDs.Add(pair.Value, pair.Key);
                    }
                }

                Dictionary<string, int> ProductCodeIDs = new Dictionary<string, int>();
                foreach (KeyValuePair<int, Product> pair in DicProduct) {
                    if (!ProductCodeIDs.ContainsKey(pair.Value.ProductCode)) {
                        ProductCodeIDs.Add(pair.Value.ProductCode, pair.Key);
                    }
                }  

                for (int i = initColumn; i < range.Columns.Count; i = i + 2) {
                    string marketName = (string)(range.Cells[1, i] as Range).Value2;
                    // reach to end of row in excel file.
                    if (string.IsNullOrEmpty(marketName)) {
                        break;
                    }

                    // Check and add market if it do not existed
                    if (!existedMarketIdName.ContainsValue(marketName)) {
                        CreateMarket(existedMarkets, context, marketName);
                    }
                    if (!marketNameIDs.ContainsKey(marketName)) {
                        continue;
                    }
                    Market market = existedMarkets[marketNameIDs[marketName]];
                    if (market.Status == 'I') {
                        continue;
                    }

                    for (int j = initRow; j < range.Rows.Count; j++) {
                        string code = DicProductCode[j];
                        double? saleRate = (range.Cells[j, i + 1] as Range).Value2;
                        double? quantityLeft = (range.Cells[j, i] as Range).Value2;
                        Product p = DicProduct[ProductCodeIDs[code]];
                        if (p.Status == 'I') {
                            continue;
                        }
                       
                        saleRate = saleRate != null ? saleRate : 0;
                        quantityLeft = quantityLeft != null ? quantityLeft : 0;
                        // skip this cell, this will be add at the end of function
                        if (saleRate <= 0 && quantityLeft <= 0) {
                            continue; // skip market with code have sale rate and quanlity is empty
                        }

                        int inventory = Convert.ToInt32(quantityLeft);

                        if (market.Type == 'C') {// sum all invetory of all store center an skip
                            if (!DicStoreInventory.ContainsKey(p.ID)) {
                                DicStoreInventory.Add(p.ID, 0);
                            }
                            DicStoreInventory[p.ID] += inventory;
                            continue;
                        }

                        // sum saleRate of all market to use in order product
                        if (!sumSaleRate.ContainsKey(p.ID)) {
                            sumSaleRate.Add(p.ID, 0);
                        }
                        sumSaleRate[p.ID] += saleRate.Value;
                        UpdateAndSaveReportResult(pl, DicData, lastUpdate, context, market, saleRate, p, inventory);
                    }
                }

                // save store
                if (sumSaleRate.Count > 0) {
                    foreach (int ProductId in sumSaleRate.Keys) {
                        if (DicProduct[ProductId].ProductKind1 != null) { // don't comput for new product.
                            ProductOrder order = new ProductOrder();
                            if (!DicStoreProduct.ContainsKey(ProductId)) {
                                context.ProductOrders.InsertOnSubmit(order);
                                DicStoreProduct.Add(ProductId, order);
                            }
                            order = DicStoreProduct[ProductId];
                            order.ProductId = ProductId;
                            order.ProductKindId = DicProduct[ProductId].ProductKind.Value;
                            order.ProductLineId = pl.ID;
                            order.SumSaleRate = sumSaleRate[ProductId];
                            int inventory = 0;
                            if (DicStoreInventory.ContainsKey(ProductId)) {
                                inventory = DicStoreInventory[ProductId];
                            }
                            order.ImportInventory = inventory;
                            order.ComputedInventory = inventory;
                            order.LastUpdatedDate = DateTime.Now;
                        }
                    }
                }
                //pl.LastUpdatedDate = DateTime.Now;
                context.SubmitChanges();
                return true;
            } catch (Exception ex) {
                // TODO
                return false;
            } finally {
                if (_wbk != null) {
                    _wbk.Close(false, Missing.Value, Missing.Value);
                }
                if (app != null) {
                    app.Quit();
                }
                releaseObject(_wsh);
                releaseObject(_wbk);
                releaseObject(app);
            }
        }

        private static void UpdateAndSaveReportResult(ProductLine pl,
            Dictionary<String, MarketProductImport> DicData, DateTime lastUpdate, StoreTransportDataContext context, 
            Market market, double? saleRate, Product p, int inventory) {
            // Note: if it is new product, compute or not?
            if (p.ProductKind1 != null) {
                MarketProductImport link = new MarketProductImport();
                string key = market.ID + "-" + p.ID;
                if (!DicData.ContainsKey(key)) {
                    DicData.Add(key, link);
                    context.MarketProductImports.InsertOnSubmit(link);
                }
                link = DicData[key];
                link.AreaId = market.Area.Value;
                link.RegionId = market.Area1.Region.Value;
                link.ProductId = p.ID;
                link.ProductKindId = p.ProductKind.HasValue ? p.ProductKind.Value : 0;
                link.ProductLineId = p.ProductKind1 != null ? p.ProductKind1.ProductLine : 0;
                link.MarketId = market.ID;
                link.StoreAmount = inventory;
                link.SaleRate = saleRate.Value;
            }
        }

        private static void CreateMarket(Dictionary<int, Market> existedMarkets, 
            StoreTransportDataContext context, string marketName) {
            Market m = new Market();
            m.MarketName = marketName;
            m.Status = 'I';
            m.ValidData = 'I';
            m.MarketCode = marketName;
            context.Markets.InsertOnSubmit(m);
            context.SubmitChanges();
            existedMarkets.Add(m.ID, m);
        }

        private static Dictionary<int, Product> UpdateProducts(ProductLine pl, 
            Dictionary<int, string> DicProductCode, Dictionary<int, Product> DicPrdouct, StoreTransportDataContext context, Range range) {
            var query = context.Products.Where(p => pl.ID == p.ProductKind1.ProductLine);
            // Check and save new product, And deactive Product if it not imported from excel 
            DicPrdouct = query.ToDictionary(p => p.ID);
            Dictionary<int, string> DicProductIdCode = query.ToDictionary(p => p.ID, p => p.ProductCode);
            foreach (var code in DicProductCode) {
                if (!DicProductIdCode.ContainsValue(code.Value)) {
                    Product p = new Product();
                    p.ProductCode = code.Value;
                    p.ProductName = (range.Cells[code.Key, 2] as Range).Value2;
                    p.Status = 'I';
                    p.ValidData = 'I';
                    context.Products.InsertOnSubmit(p);
                    context.SubmitChanges();
                    DicPrdouct.Add(p.ID, p);
                    DicProductIdCode.Add(p.ID, p.ProductCode);
                }
            }

            foreach (var p in DicProductIdCode) {
                if (!DicProductCode.ContainsValue(p.Value)) {
                    DicPrdouct[p.Key].Status = Status.I.ToString()[0];
                } else { 
                    // Need  to change set status is active for all products in excel file.
                }
            }
            context.SubmitChanges();
            return DicPrdouct;
        }

        private static void releaseObject(object obj) {
            try {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            } catch (Exception ex) {
                obj = null;
            } finally {
                GC.Collect();
            }
        }

        public static void ImportMarkets(string filePath) {
            List<Market> marketList = new List<Market>();
            int MarketCodeColum = 1;
            int MarketNameColum = 2;
            int ShipmentDayColum = 3;
            int TypeColum = 4;
            int KindColum = 5;
            int RegionNameColum = 6;
            int AreaNameColumn = 7;
            int StatusColumn = 8;
            Application app = null;
            Workbooks wbks = null;
            _Workbook _wbk = null;
            Sheets shs = null;
            _Worksheet _wsh = null;
            try {
                app = new Application();
                wbks = app.Workbooks;
                _wbk = wbks.Add(filePath);
                shs = _wbk.Sheets;
                _wsh = (_Worksheet) shs.get_Item(1);
                Range range = _wsh.UsedRange;
                StoreTransportDataContext context = CommonHelper.GetContext();
                List<string> existedMarketList = context.Markets.Select(c => c.MarketName).ToList();
                Dictionary<string,Region> regions = context.Regions.ToDictionary(r => r.RegionName);
                Dictionary<string, Area> areas = context.Areas.ToDictionary(a => a.AreaName);
                Dictionary<string, MarketKind> kinds = context.MarketKinds.ToDictionary(r => r.MarketKindName);
                for (int i = 2; i < range.Rows.Count; i++) {

                    string marketName = (string)(range.Cells[i, MarketNameColum] as Range).Value2;
                    if (string.IsNullOrEmpty(marketName)) {
                        break;
                    }
                    if (!existedMarketList.Contains(marketName)) {
                        existedMarketList.Add(marketName);
                        Market market = new Market();
                        market.MarketName = marketName;
                        market.MarketCode = Convert.ToString((range.Cells[i, MarketCodeColum] as Range).Value2); // TODO Fix later
                        double shipmentDayData = (range.Cells[i, ShipmentDayColum] as Range).Value2 != null
                            ? (range.Cells[i, ShipmentDayColum] as Range).Value2 : 0;
                        String type = (range.Cells[i, TypeColum] as Range).Value2;
                        String kind = (range.Cells[i, KindColum] as Range).Value2;
                        String areaName = (range.Cells[i, AreaNameColumn] as Range).Value2;
                        String regionName = (range.Cells[i, RegionNameColum] as Range).Value2;
                        String status = (range.Cells[i, StatusColumn] as Range).Value2;

                        int shipmentDay = Convert.ToInt32(shipmentDayData);

                        if (shipmentDay >= 2 && shipmentDay <= 7) {
                            market.TransportDay = shipmentDay;
                        }

                        if (type != null && Enum.IsDefined(typeof(MarketType), type))
                        {
                            market.Type = type[0];
                        }

                        if (!string.IsNullOrEmpty(kind))
                        {
                            if (!kinds.ContainsKey(kind))
                            {
                                MarketKind marketKind = new MarketKind();
                                marketKind.MarketKindName = kind;
                                kinds.Add(kind, marketKind);
                            }
                            market.Kind = kinds[kind].ID;
                            market.MarketKind = kinds[kind];
                        }

                        if (!string.IsNullOrEmpty(areaName))
                        {
                            if (!areas.ContainsKey(areaName))
                            {
                                Area a = new Area();
                                a.AreaName = areaName;
                                if (!string.IsNullOrEmpty(regionName))
                                {
                                    if (!regions.ContainsKey(regionName))
                                    {
                                        Region re = new Region();
                                        re.RegionName = regionName;
                                        context.Regions.InsertOnSubmit(re);
                                        regions.Add(regionName, re);
                                    }
                                    a.Region = regions[regionName].ID;
                                    a.Region1 = regions[regionName];
                                   
                                }
                                context.Areas.InsertOnSubmit(a);
                                areas.Add(areaName, a);
                            }
                            market.Area = areas[areaName].ID;
                            market.Area1 = areas[areaName];
                        }

                        if (!string.IsNullOrEmpty(status))
                        {
                            market.Status = status[0];
                        }
                        else
                        {
                            market.Status = 'A';
                        }
                        context.Markets.InsertOnSubmit(market);
                    }
                }
                context.SubmitChanges();
            } catch (Exception ex) {
                // TODO
            } finally {
                if (_wbk != null) {
                    _wbk.Close(false, Missing.Value, Missing.Value);
                }
                if (app != null) {
                    app.Quit();
                }
                releaseObject(_wsh);
                releaseObject(_wbk);
                releaseObject(app);
            }
        }

        public static string ImportProducts(string filePath, int productLineID) {
            List<Product> productList = new List<Product>();
            int productCodeColum = 1;
            int productNameColum = 2;
            int productKindColum = 3;
            int statusColum = 4;
            int subType1Colum = 5;
            int subType2Colum = 6;
            int subType3Colum = 7;
            int subType4Colum = 8;
            Application app = null;
            Workbooks wbks = null;
            _Workbook _wbk = null;
            Sheets shs = null;
            _Worksheet _wsh = null;
            string message = string.Empty;
            try {
                app = new Application();
                wbks = app.Workbooks;
                _wbk = wbks.Add(filePath);
                shs = _wbk.Sheets;
                _wsh = (_Worksheet)shs.get_Item(1);
                Range range = _wsh.UsedRange;
                StoreTransportDataContext context = CommonHelper.GetContext();
                Dictionary<int, string> DicProductKind = context.ProductKinds.Where(c => c.ProductLine == productLineID).
                    ToDictionary(c => c.ID, c => c.ProductKindName);
                List<string> productCodeList = context.Products.Select(c => c.ProductCode).ToList();

                for (int i = 2; i < range.Rows.Count; i++) {

                    string productCode = Convert.ToString((range.Cells[i, productCodeColum] as Range).Value2);
                    if (string.IsNullOrEmpty(productCode)) {
                        break;
                    }
                    if (!productCodeList.Contains(productCode)) {
                        productCodeList.Add(productCode);
                        Product product = new Product();
                        product.ProductCode = productCode;
                        string productName = (range.Cells[i, productNameColum] as Range).Value2;
                        string productkind = (range.Cells[i, productKindColum] as Range).Value2;
                        product.Status = Status.A.ToString()[0];
                        product.ValidData = ValidData.V.ToString()[0];

                        string status = (range.Cells[i, statusColum] as Range).Value2;


                        if (status != null && Enum.IsDefined(typeof(Status), status))
                        {
                            product.Status = status[0];
                        }
                        else
                        {
                            product.Status = 'A';
                        }
                       

                        if (!string.IsNullOrEmpty(productName)) {
                            product.ProductName = productName;
                        }

                        string subType1 = Convert.ToString((range.Cells[i, subType1Colum] as Range).Value2);
                        string subType2 = Convert.ToString((range.Cells[i, subType2Colum] as Range).Value2);
                        string subType3 = Convert.ToString((range.Cells[i, subType3Colum] as Range).Value2);
                        string subType4 = Convert.ToString((range.Cells[i, subType4Colum] as Range).Value2);

                        if (!string.IsNullOrEmpty(productkind)) {
                            if (!DicProductKind.ContainsValue(productkind)) {
                                ProductKind kind = new ProductKind();
                                kind.ProductLine = productLineID;
                                kind.ProductKindName = productkind;
                                kind.MainType = productkind;
                                kind.SubType1 = subType1;
                                kind.SubType2 = subType2;
                                kind.SubType3 = subType3;
                                kind.SubType4 = subType4;
                                context.ProductKinds.InsertOnSubmit(kind);
                                context.SubmitChanges();
                                DicProductKind.Add(kind.ID, productkind);
                            }
                            int id = DicProductKind.Where(c => c.Value == productkind).First().Key;
                            product.ProductKind = id;
                        }
                        context.Products.InsertOnSubmit(product);
                    }
                }
                context.SubmitChanges();
                return message;
            } catch (Exception ex) {
                message = ex.ToString();
                return message;
            } finally {
                if (_wbk != null) {
                    _wbk.Close(false, Missing.Value, Missing.Value);
                }
                if (app != null) {
                    app.Quit();
                }
                releaseObject(_wsh);
                releaseObject(_wbk);
                releaseObject(app);
            }
        }

        public static string ExportData(System.Data.DataTable table, bool showHeader,  string folderPath, string fileNameParten) {
            string filePath = folderPath + "\\" + string.Format(fileNameParten, DateTime.Now.ToString(ExportTimeFormat));
            Application app = null;
            Workbooks wbks = null;
            _Workbook _wbk = null;
            Sheets shs = null;
            _Worksheet _wsh = null;
            try {
                app = new Application();
                wbks = app.Workbooks;
                _wbk = wbks.Add(true);
                shs = _wbk.Sheets;
                _wsh = (_Worksheet)shs.get_Item(1);
                // build header row
                int maxColumn = table.Columns.Count;
                ((Range)_wsh.Rows[1, Missing.Value]).RowHeight = 100;
                ((Range)_wsh.Rows[1, Missing.Value]).WrapText = true;
                ((Range)_wsh.Rows[1, Missing.Value]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                ((Range)_wsh.Rows[1, Missing.Value]).VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                ((Range)_wsh.Rows[1, Missing.Value]).Font.Bold = true;
                ((Range)_wsh.Rows[1, Missing.Value]).Interior.ColorIndex = 15;

                // 1 is header row.
                
                string maxRow = "A" + table.Rows.Count + 1;
                // set text format for first column in excel
                _wsh.Application.Range["A1", maxRow].Cells.NumberFormat = "@";

                int rowIndex = 1;
                if (showHeader) {
                    for (int i = 0; i < maxColumn; i++) {
                        _wsh.Cells[1, i + 1] = table.Columns[i].ColumnName;
                    }
                    rowIndex = 2;
                }
               
                // fill data each row
                foreach (DataRow row in table.Rows) {
                    for (int i = 0; i < maxColumn; i++) {
                        _wsh.Cells[rowIndex, i + 1] = row[i];
                        //if (i == 0) {
                        //    ((Range)_wsh.Rows[rowIndex, i + 1]).NumberFormat = "@";
                        //}
                    }
                    rowIndex++;
                }
                _wbk.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                        Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            } catch (Exception ex) {
                return ex.Message;
            } finally {
                if (_wbk != null) {
                    _wbk.Close(false, Missing.Value, Missing.Value);
                }
                if (app != null) {
                    app.Quit();
                }
                releaseObject(_wsh);
                releaseObject(_wbk);
                releaseObject(app);
            }
            return string.Empty;
        }
    }
}
